package kam.fire;

import java.util.ArrayList;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * the database helper handles all interaction with the database
 * 
 * @author palepail
 * 
 */
public class DatabaseHelper extends SQLiteOpenHelper {
	static final String dbName = "KamfireDB";
	static final int DBVersion = 46;

	static final String roomsTable = "Rooms";
	static final String colRoomIndex = "RoomIndex";
	static final String colRoomId = "RoomId";
	static final String colRoomName = "RoomName";
	static final String colRoomTopic = "RoomTopic";
	static final String colRoomCreated = "Created";
	static final String colRoomUpdated = "Updated";
	static final String colRoomLimit = "RoomLimit";
	static final String colRoomFull = "RoomFull";
	static final String colRoomOpen = "RoomOpen";
	static final String colUser = "User";

	static final String messageTable = "Messages";
	static final String colMessageIndex = "MessageIndex";
	static final String colMessageId = "MessageId";
	static final String colMessageType = "MessageType";
	static final String colMessageBody = "MessageBody";
	static final String colMessageAge = "Age";
	static final String colMessageStarred = "Starred";
	static final String colMessageUserId = "UserId";
	static final String colMessageRoomId = "RoomId";
	static final String colMessageUserIndex = "UserIndex";
	static final String colMessageRoomIndex = "RoomIndex";

	static final String userTable = "UserTable";
	static final String colUserIndex = "UserIndex";
	static final String colUserId = "UserId";
	static final String colUserName = "UserName";
	static final String colUserApiKey = "UserApiKey";
	static final String colUserCampsite = "UserCampsite";
	static final String colUserCreatedAt = "UserCreatedAt";
	static final String colUserType = "UserType";
	static final String colUserEmail = "UserEmail";
	static final String colUserAdmin = "UserAdmin";
	static final String colUserAvatar = "UserAvatar";

	static final String optionsTable = "OptionsTable";
	static final String colOptionsIndex = "OptionsIndex";
	static final String colCampsiteBox = "CampsiteBox";
	static final String colTokenBox = "TokenBox";
	static final String colAutoSaveFiles = "AutoSave";
	static final String colUserOptionsIndex = "UserIndex";
	static final String colUserOptionsId = "UserId";

	static final String uploadsTable = "UploadsTable";
	static final String colUploadIndex = "UploadIndex";
	static final String colUploadId = "UploadId";
	static final String colUploadSize = "UploadSize";
	static final String colUploadType = "UploadType";
	static final String colUploadDate = "UploadDate";
	static final String colUploadName = "UploadName";
	static final String colUploadRoomId = "UploadRoomId";
	static final String colUploadUserId = "UploadUserId";

	static final String colUploadURL = "UploadURL";

	private static DatabaseHelper instance;

	public DatabaseHelper(Context context) {
		super(context, dbName, null, DBVersion);
		// TODO Auto-generated constructor stub
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// Order of columns must not be changed

		db.execSQL("CREATE TABLE IF NOT EXISTS " + userTable + " (" + colUserIndex
				+ " INTEGER PRIMARY KEY AUTOINCREMENT , " + colUserId + " INTEGER , " + colUserName + " TEXT , "
				+ colUserApiKey + " TEXT, " + colUserCampsite + " TEXT , " + colUserCreatedAt + " TEXT , "
				+ colUserType + " TEXT , " + colUserEmail + " TEXT , " + colUserAdmin + " INTEGER , " + colUserAvatar
				+ " TEXT);");

		db.execSQL("CREATE TABLE IF NOT EXISTS " + optionsTable + " (" + colOptionsIndex
				+ " INTEGER PRIMARY KEY AUTOINCREMENT , " + colCampsiteBox + " INTEGER DEFAULT 0 , " + colTokenBox
				+ " INTEGER DEFAULT 0 , " + colAutoSaveFiles + " INTEGER DEFAULT 0 , " + colUserOptionsId
				+ " INTEGER , " + colUserOptionsIndex + " INTEGER NOT NULL ,FOREIGN KEY (" + colUserOptionsIndex
				+ ") REFERENCES " + userTable + " (" + colUserIndex + "));");

		db.execSQL("CREATE TABLE IF NOT EXISTS " + roomsTable + " (" + colRoomIndex
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + colRoomId + " INTEGER, " + colRoomName + " TEXT, "
				+ colRoomTopic + " TEXT, " + colRoomCreated + " INTEGER, " + colRoomUpdated + " INTEGER, "
				+ colRoomLimit + " INTEGER, " + colRoomFull + " INTEGER, " + colRoomOpen + " INTEGER, " + colUser
				+ " INTEGER NOT NULL ,FOREIGN KEY (" + colUser + ") REFERENCES " + userTable + " (" + colUserIndex
				+ "));");

		db.execSQL("CREATE TABLE IF NOT EXISTS " + uploadsTable + " (" + colUploadIndex
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + colUploadId + " INTEGER, " + colUploadSize + " INTEGER, "
				+ colUploadURL + " TEXT, " + colUploadType + " TEXT, " + colUploadDate + " TEXT, " + colUploadName + " TEXT, " + colUploadRoomId
				+ " INTEGER NOT NULL," + colUploadUserId + " INTEGER NOT NULL);");

		db.execSQL("CREATE TABLE IF NOT EXISTS " + messageTable + " (" + colMessageIndex
				+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + colMessageId + " INTEGER, " + colMessageType + " TEXT, "
				+ colMessageBody + " TEXT, " + colMessageAge + " TEXT, " + colMessageStarred + " INTEGER, "
				+ colMessageUserId + " INTEGER, " + colMessageRoomId + " INTEGER, " + colMessageUserIndex
				+ " INTEGER, " + colMessageRoomIndex + " INTEGER NOT NULL ,FOREIGN KEY (" + colMessageRoomIndex
				+ ") REFERENCES " + roomsTable + " (" + colRoomIndex + "));");

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + uploadsTable);
		db.execSQL("DROP TABLE IF EXISTS " + messageTable);
		db.execSQL("DROP TABLE IF EXISTS " + roomsTable);
		onCreate(db);
	}

	// ============================USER
	Cursor getAllUsers() {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cur = db.rawQuery("SELECT " + colUserId + " as _id, " + colUserName + " from " + userTable,
				new String[] {});
		return cur;
	}

	/**
	 * parses User object and adds to database
	 * 
	 * @param user
	 */
	public void addUser(User user) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues cv = new ContentValues();

		cv.put(colUserId, user.getId());
		cv.put(colUserIndex, user.getUserIndex());
		cv.put(colUserName, user.getName());
		cv.put(colUserApiKey, user.getApiAuthToken());
		cv.put(colUserCampsite, user.getCampsite());
		cv.put(colUserCreatedAt, user.getCreatedAt());
		cv.put(colUserEmail, user.getEmailAdress());
		cv.put(colUserAdmin, (user.getAdmin()) ? 1 : 0);
		cv.put(colUserAvatar, user.getAvatarUrl());
		long x = db.replace(userTable, null, cv);
		if (x != -1) {
			user.setUserIndex((int) x);
		}
		closeDB(db);

	}

	/**
	 * compiles user object from database given a user index.
	 * 
	 * @param Index
	 *            the user to find's index
	 * @return
	 */
	public User getUserByIndex(int Index) {
		SQLiteDatabase db = this.getReadableDatabase();
		User user = new User();

		Cursor cursor = db.query(userTable, new String[] { "*" }, colUserIndex + "=" + Index, null, null, null, null);

		try {
			// looping through all rows and adding to room
			if (cursor.moveToFirst()) {
				do {

					user.setUserIndex(Integer.parseInt(cursor.getString(0)));
					user.setId(Integer.parseInt(cursor.getString(1)));
					user.setName(cursor.getString(2));
					user.setApiAuthToken(cursor.getString(3));
					user.setCampsite(cursor.getString(4));
					user.setCreatedAt(cursor.getString(5));
					user.setType(cursor.getString(6));
					user.setEmailAdress(cursor.getString(7));
					user.setAdmin(Boolean.getBoolean(cursor.getString(8)));
					user.setAvatarUrl(cursor.getString(9));
				} while (cursor.moveToNext());
			}
		} finally {
			cursor.close();
			closeDB(db);
		}

		return user;
	}

	/**
	 * returns the user id given a username
	 * 
	 * @param User
	 *            the username
	 * @return
	 */
	public int getUserId(String User) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db.query(userTable, new String[] { colUserId }, colUserName + "=" + User, null, null, null, null);
		int i = -1;
		try {
			if (c.moveToFirst()) {
				i = c.getInt(c.getColumnIndex(colUserId));
			}
		} finally {
			c.close();
			closeDB(db);
		}
		return i;

	}

	/**
	 * returns the user index given a user id
	 * 
	 * @param userId
	 * @return
	 */
	public int getUserIndex(int userId) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor c = db.query(userTable, new String[] { colUserIndex }, colUserId + "=" + userId, null, null, null, null);
		int i = -1;
		try {
			if (c.moveToFirst()) {
				i = c.getInt(c.getColumnIndex(colUserIndex));
			}
		} finally {
			c.close();
			closeDB(db);
		}
		return i;

	}

	/**
	 * returns the username given a user id
	 * 
	 * @param UserId
	 * @return
	 */
	public String getUserName(int UserId) {
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor c = db
				.query(userTable, new String[] { colUserName }, colUserId + " = " + UserId, null, null, null, null);
		String s = "";
		try {
			if (c.moveToFirst()) {
				s = c.getString(c.getPosition());
			}
		} finally {
			c.close();
			closeDB(db);
		}
		return s;

	}

	// =======================Rooms
	/**
	 * adds all rooms in an ArrayList of rooms to the database
	 * 
	 * @param rooms
	 */
	public void addRoom(ArrayList<Room> rooms) {

		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();

		// for (int x = 0; x < rooms.size(); x++) {
		for (Room room : rooms) {
			// RoomIndex Auto-Increments
			cv.put(colRoomId, room.getId());
			cv.put(colRoomName, room.getName());
			cv.put(colRoomTopic, room.getTopic());
			cv.put(colRoomCreated, room.getDateCreated());
			cv.put(colRoomUpdated, room.getDateUpdated());
			cv.put(colRoomLimit, room.getLimit());
			cv.put(colUser, room.getUserIndex());
			cv.put(colRoomFull, room.getFull());
			cv.put(colRoomOpen, room.getOpen());

			long i = db.replace(roomsTable, null, cv);
			if (i != -1) {
				room.setIndex((int) i);
			}

		}
		closeDB(db);

	}

	/**
	 * returns the roomId given a roomIndex
	 * 
	 * @param index
	 * @return
	 */
	public int getRoomId(int index) {
		SQLiteDatabase db = this.getReadableDatabase();
		int id = getRoomIdFromIndex(db, index);
		closeDB(db);
		return id;

	}

	/**
	 * returns the roomId given a roomIndex and db
	 * 
	 * @param db
	 * @param index
	 * @return
	 */
	private int getRoomIdFromIndex(SQLiteDatabase db, int index) {
		String where = colRoomIndex + " = " + index;
		Cursor c = db.query(roomsTable, new String[] { colRoomId }, where, null, null, null, null);
		int i = -1;
		try {
			if (c.moveToFirst()) {
				i = c.getInt(c.getColumnIndex(colRoomId));
			}
		} finally {
			c.close();
			closeDB(db);
		}
		return i;

	}

	/**
	 * returns a roomIndex given a roomId.
	 * 
	 * @param id
	 * @return
	 */
	public int getRoomIndex(int id) {
		SQLiteDatabase db = this.getReadableDatabase();
		int index = getRoomIndexFromId(db, id);
		closeDB(db);
		return index;

	}

	/**
	 * returns the roomIndex given the roomId and database
	 * 
	 * @param db
	 * @param id
	 * @return
	 */
	private int getRoomIndexFromId(SQLiteDatabase db, int id) {

		String where = colRoomId + " = " + id;
		Cursor c = db.query(roomsTable, new String[] { colRoomIndex }, where, null, null, null, null);
		int i = 0;
		try {
			if (c.moveToFirst()) {
				i = c.getInt(c.getColumnIndex(colRoomIndex));
			}
		} finally {
			c.close();
			closeDB(db);
		}

		return i;

	}

	/**
	 * compiles and returns a room from the database given a room index.
	 * 
	 * @param roomIndex
	 * @return
	 */
	public Room getRoomByIndex(int roomIndex) {

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.query(roomsTable, new String[] { "*" }, "RoomIndex =" + roomIndex, null, null, null, null);

		Room room = new Room();
		try {
			// looping through all rows and adding to room
			if (cursor.moveToFirst()) {
				do {

					room.setIndex(getIntValue(cursor, colRoomIndex));
					room.setId(getIntValue(cursor, colRoomId));
					room.setName(getStringValue(cursor, colRoomName));
					room.setTopic(getStringValue(cursor, colRoomTopic));
					room.setDate(getStringValue(cursor, colRoomCreated));
					room.setDateUpdated(getStringValue(cursor, colRoomUpdated));
					room.setLimit(getIntValue(cursor, colRoomLimit));
					room.setUserIndex(getIntValue(cursor, colUser));
					room.setFull(getBoolValue(cursor, colRoomFull));
					room.setOpen(getBoolValue(cursor, colRoomOpen));
				} while (cursor.moveToNext());
			}
			// return contact list
		} finally {
			cursor.close();
			closeDB(db);
		}
		return room;
	}

	/**
	 * returns all rooms associated with a user given a user index
	 * 
	 * @param userIndex
	 * @return
	 */
	public ArrayList<Room> getRoomList(int userIndex) {
		ArrayList<Room> roomList = new ArrayList<Room>();
		// Select All Query

		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db.query(roomsTable, new String[] { "*" }, "User =" + userIndex, null, null, null, null);
		try {
			// looping through all rows and adding to room
			if (cursor.moveToFirst()) {
				do {
					Room room = new Room();
					room.setIndex(getIntValue(cursor, colRoomIndex));
					room.setId(getIntValue(cursor, colRoomId));
					room.setName(getStringValue(cursor, colRoomName));
					room.setTopic(getStringValue(cursor, colRoomTopic));
					room.setDate(getStringValue(cursor, colRoomCreated));
					room.setDateUpdated(getStringValue(cursor, colRoomUpdated));
					room.setLimit(getIntValue(cursor, colRoomLimit));
					room.setUserIndex(getIntValue(cursor, colUser));
					room.setFull(getBoolValue(cursor, colRoomFull));
					room.setOpen(getBoolValue(cursor, colRoomOpen));
					// Adding room to list
					roomList.add(room);
				} while (cursor.moveToNext());
			}
		} finally {
			cursor.close();
			closeDB(db);
		}
		return roomList;
	}
	public ArrayList<Integer> getRoomIndexList(int userIndex) {
		ArrayList<Integer> roomIndexList = new ArrayList<Integer>();
		// Select All Query

		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db.query(roomsTable, new String[] { "*" }, "User =" + userIndex, null, null, null, null);
		try {
			// looping through all rows and adding to room
			if (cursor.moveToFirst()) {
				do {
					roomIndexList.add(getIntValue(cursor, colRoomIndex));
				} while (cursor.moveToNext());
			}
		} finally {
			cursor.close();
			closeDB(db);
		}
		return roomIndexList;
	}

	/**
	 * deletes a single room from the database
	 * 
	 * @param room
	 */
	public void deleteRoom(Room room) {
		SQLiteDatabase db = this.getWritableDatabase();
		deleteMessagesFromRoom(db, room.getIndex());
		db.delete(roomsTable, colRoomId + "=?", new String[] { String.valueOf(room.getId()) });
		closeDB(db);

	}

	/**
	 * deletes all rooms and messages from a user
	 * 
	 * @param userIndex
	 */
	public void deleteRoomsFromUser(int userIndex) {
		SQLiteDatabase db = this.getWritableDatabase();
		String where = colUser + " = " + userIndex;

		db.delete(roomsTable, where, null);
		db.delete(messageTable, where, null);
		closeDB(db);
	}

	// ===================Messages
	/**
	 * add messages to database given a ArrayList of messages
	 * 
	 * @param messages
	 */
	public void addMessages(ArrayList<Message> messages) {

		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();

		for (Message message : messages) {
			// messageIndex Auto-Increments
			cv.put(colMessageId, message.getId());
			cv.put(colMessageType, message.getType());
			cv.put(colMessageBody, message.getBody());
			cv.put(colMessageAge, message.getDate());
			cv.put(colMessageStarred, message.getStarred());
			cv.put(colMessageUserId, message.getUserId());
			cv.put(colMessageRoomId, message.getRoomId());
			cv.put(colMessageUserIndex, getUserIndex(message.getUserId()));
			cv.put(colMessageRoomIndex, getRoomIndexFromId(db, message.getRoomId()));

			long x = db.replace(messageTable, null, cv);
			if (x != -1) {
				message.setIndex((int) x);
			}
		}
		closeDB(db);

	}

	/**
	 * deletes all messages from a room given a db and room index
	 * 
	 * @param db
	 * @param index
	 */
	private void deleteMessagesFromRoom(SQLiteDatabase db, int index) {

		db.delete(messageTable, colMessageRoomIndex + "=?", new String[] { String.valueOf(index) });
		closeDB(db);
	}

	/**
	 * return the id of the last message in a room givent he room index
	 * 
	 * @param roomIndex
	 * @return
	 */
	public int getLastMessage(int roomIndex) {
		int messageId = 0;

		String selectQuery = colMessageRoomIndex + " = " + roomIndex;
		String[] column = { "MAX(" + colMessageId + ") AS " + colMessageId };
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.query(messageTable, column, selectQuery, null, null, null, null);
		try {
			if (cursor.moveToFirst()) {
				messageId = getIntValue(cursor, colMessageId);
			}
		} finally {
			cursor.close();
			closeDB(db);
		}
		return messageId;
	}

	/**
	 * returns all the messages in a room given the room index
	 * 
	 * @param roomIndex
	 * @return
	 */
	public ArrayList<Message> getMessagesByRoom(int roomIndex) {
		ArrayList<Message> messageList = new ArrayList<Message>();
		// Select All Query

		String selectQuery = colMessageRoomIndex + " = " + roomIndex;
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(messageTable, null, selectQuery, null, null, null, null);

		// looping through all rows and adding to message
		try {
			if (cursor.moveToFirst()) {
				do {
					Message message = new Message();
					message.setIndex(getIntValue(cursor, colMessageIndex));
					message.setId(getIntValue(cursor, colMessageId));
					message.setType(getStringValue(cursor, colMessageType));
					message.setBody(getStringValue(cursor, colMessageBody));
					message.setDate(getStringValue(cursor, colMessageAge));
					message.setStarred(getBoolValue(cursor, colMessageStarred));
					message.setUserId(getIntValue(cursor, colMessageUserId));
					message.setRoomId(getIntValue(cursor, colMessageRoomId));
					message.setUserIndex(getIntValue(cursor, colMessageUserIndex));
					message.setRoomIndex(getIntValue(cursor, colMessageRoomIndex));

					messageList.add(message);
				} while (cursor.moveToNext());
			}
		} finally {
			cursor.close();
			closeDB(db);
		}
		return messageList;
	}

	// ================ OPTIONS

	/**
	 * adds an options object to the database
	 * 
	 * @param options
	 */
	public void addOptions(Options options) {

		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues cv = new ContentValues();
		cv.put(colOptionsIndex, 0);
		cv.put(colCampsiteBox, options.getCampsiteBox());
		cv.put(colTokenBox, options.getTokenBox());
		cv.put(colAutoSaveFiles, options.getAutoSaveFiles());
		cv.put(colUserOptionsIndex, options.getUserIndex());
		cv.put(colUserOptionsId, options.getUserId());

		db.replace(optionsTable, null, cv);
		closeDB(db);
	}

	/**
	 * returns the value of the tokenbox given the userindex
	 * 
	 * @param index
	 * @return
	 */
	public boolean getTokenBox(int index) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.query(optionsTable, new String[] { colTokenBox }, colUserOptionsIndex + " = " + index, null,
				null, null, null);
		int i = -1;
		try {
			// read data from the cursor in here
			if (cursor.moveToFirst()) {
				i = cursor.getInt(1);
			}
		} finally {
			cursor.close();
			closeDB(db);
		}
		return (i != 0);
	}

	/**
	 * returns the value of the campsite box given the userindex
	 * 
	 * @param index
	 * @return
	 */
	public boolean getCampsiteBox(int index) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.query(optionsTable, new String[] { colCampsiteBox }, colUserOptionsIndex + " = " + index,
				null, null, null, null);
		int i = -1;
		try {
			if (cursor.moveToFirst()) {
				i = cursor.getInt(1);
			}

			cursor.close();
			closeDB(db);

		} finally {
			cursor.close();
			closeDB(db);
		}
		return (i != 0);
	}

	/**
	 * returns the value of the autosave box given the user index
	 * 
	 * @param index
	 * @return
	 */
	public boolean getAutoSave(int index) {
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.query(optionsTable, new String[] { colAutoSaveFiles }, colUserOptionsIndex + " = " + index,
				null, null, null, null);
		int i = -1;
		try {
			if (cursor.moveToFirst()) {
				i = cursor.getInt(1);
			}

		} finally {
			cursor.close();
			closeDB(db);
		}

		return (i != 0);
	}

	/**
	 * compiles and returns the first entry of the options table. there should
	 * only ever be one row in the options table
	 * 
	 * @return
	 */
	public Options getOptions() {
		Options options = new Options();
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.query(optionsTable, new String[] { "*" }, null, null, null, null, null);
		try {
			if (cursor.moveToFirst()) {
				options.setCampsiteBox(getBoolValue(cursor, colCampsiteBox));
				options.setTokenBox(getBoolValue(cursor, colTokenBox));
				options.setAutoSaveFiles(getBoolValue(cursor, colAutoSaveFiles));
				options.setUserId(getIntValue(cursor, colUserOptionsId));
				options.setUserIndex(getIntValue(cursor, colUserOptionsIndex));

			}
		} finally {
			cursor.close();
			closeDB(db);
		}
		return options;
	}

	// ==========================Uploads

	public ArrayList<Upload> getUploadsByRoom(int roomIndex) {
		ArrayList<Upload> uploadList = new ArrayList<Upload>();
		// Select All Query
		SQLiteDatabase db = this.getReadableDatabase();
		String selectQuery = colUploadRoomId + " = " + getRoomId(roomIndex);

		Cursor cursor = db.query(uploadsTable, null, selectQuery, null, null, null, null);

		// looping through all rows and adding to message
		try {
			if (cursor.moveToFirst()) {
				do {
					Upload upload = new Upload();
					upload.setIndex(getIntValue(cursor, colUploadIndex));
					upload.setId(getIntValue(cursor, colUploadId));
					upload.setCreatedAt(getStringValue(cursor, colUploadDate));
					upload.setName(getStringValue(cursor, colUploadName));
					upload.setRoomId(getIntValue(cursor, colUploadRoomId));
					upload.setUserId(getIntValue(cursor, colUploadUserId));
					upload.setSize(getIntValue(cursor, colUploadSize));
					upload.setType(getStringValue(cursor, colUploadType));
					upload.setUrl(getStringValue(cursor, colUploadURL));

					uploadList.add(upload);
				} while (cursor.moveToNext());
			}
		} finally {
			cursor.close();
			closeDB(db);
		}
		return uploadList;
	}
	
	public int getUploadSize(int uploadId) {
		int size = 0;
		// Select All Query
		SQLiteDatabase db = this.getReadableDatabase();
		String selectQuery = colUploadId + " = " + uploadId;

		Cursor cursor = db.query(uploadsTable, null, selectQuery, null, null, null, null);

		// looping through all rows and adding to message
		try {
			if (cursor.moveToFirst()) {
				
					size = getIntValue(cursor, colUploadSize);		
			}
		} finally {
			cursor.close();
		
		}
		return size;
	}
	
	public void addUploads(ArrayList<Upload> uploads) {

		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();

		for (Upload upload : uploads) {
			// messageIndex Auto-Increments
			cv.put(colUploadId, upload.getId());
			cv.put(colUploadDate, upload.getCreatedAt());
			cv.put(colUploadName, upload.getName());
			cv.put(colUploadRoomId, upload.getRoomId());
			cv.put(colUploadUserId, upload.getUserId());
			cv.put(colUploadSize, upload.getSize());
			cv.put(colUploadType, upload.getType());
			cv.put(colUploadURL, upload.getUrl());

			long x = db.replace(uploadsTable, null, cv);
			if (x != -1) {
				upload.setIndex((int) x);
			}
		}
		closeDB(db);

	}
	public void addUpload(Upload upload) {

		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues cv = new ContentValues();

			// messageIndex Auto-Increments
			cv.put(colUploadId, upload.getId());
			cv.put(colUploadDate, upload.getCreatedAt());
			cv.put(colUploadName, upload.getName());
			cv.put(colUploadRoomId, upload.getRoomId());
			cv.put(colUploadUserId, upload.getUserId());
			cv.put(colUploadSize, upload.getSize());
			cv.put(colUploadType, upload.getType());
			cv.put(colUploadURL, upload.getUrl());

			long x = db.replace(uploadsTable, null, cv);
			if (x != -1) {
				upload.setIndex((int) x);
			
		}
		closeDB(db);

	}
	
	

	// ====================== Utility

	public static synchronized DatabaseHelper getHelper(Context context) {
		if (instance == null)
			instance = new DatabaseHelper(context);
		return instance;
	}

	private void closeDB(SQLiteDatabase db) {
		// db.close();
	}

	private int getIntValue(Cursor cursor, String columnName) {
		return cursor.getInt(cursor.getColumnIndexOrThrow(columnName));
	}

	private boolean getBoolValue(Cursor cursor, String columnName) {
		return cursor.getInt(cursor.getColumnIndexOrThrow(columnName)) != 0;
	}

	private String getStringValue(Cursor cursor, String columnName) {
		return cursor.getString(cursor.getColumnIndexOrThrow(columnName));
	}

	/**
	 * drops and recreates tables. used when only select tables need to be
	 * recreated. only used in debugging
	 */
	public void recreateTables() {
		SQLiteDatabase db = this.getWritableDatabase();
		// db.execSQL("DROP TABLE IF EXISTS " + roomsTable);
		// db.execSQL("DROP TABLE IF EXISTS " + userTable);
		// db.execSQL("DROP TABLE IF EXISTS " + messageTable);
		db.execSQL("DROP TABLE IF EXISTS " + optionsTable);
		onCreate(db);
		db.close();
	}

	private ArrayList<String> getTables(SQLiteDatabase db) {
		final ArrayList<String> tableArray = new ArrayList<String>();

		Cursor cursor = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
		try {
			if (cursor.moveToFirst()) {
				while (!cursor.isAfterLast()) {
					tableArray.add(cursor.getString(cursor.getColumnIndex("name")));
					cursor.moveToNext();
				}
			}
		} finally {
			cursor.close();
		}
		return tableArray;

	}

	private ArrayList<String> getColumns(SQLiteDatabase db, String tableName) {
		final ArrayList<String> columnArray = new ArrayList<String>();

		Cursor cursor = db.rawQuery("PRAGMA table_info(mytable)", null);
		try {
			if (cursor.moveToFirst()) {
				do {
					columnArray.add(cursor.getString(1));
				} while (cursor.moveToNext());
			}
		} finally {
			cursor.close();
		}
		return columnArray;

	}

}
